package com.social.socialservice.idal;

import com.social.socialservice.model.PostInfo;
import com.social.socialservice.unit.Pager;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.StatementType;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * 用户贴文接口方法定义
 */

@Mapper
@Component
public interface IPostDAL {
    /**
     * 用户发布帖子
     */
    @Insert("insert into ex_post(zf_parent_id,zf_quick,user_id,post_content,sp_url,tp_url,post_replylimit,post_time,post_ip,post_lan_ip," +
            "post_location,post_city,post_country,post_regionName,post_lat,post_lon)values(#{zf_parent_id},#{zf_quick},#{user_id},#{post_content}," +
            "#{sp_url},#{tp_url},#{post_replylimit},Now(),#{post_ip},#{post_lan_ip},#{post_location},#{post_city},#{post_country}," +
            "#{post_regionName},#{post_lat},#{post_lon})")
    public Integer addPost(PostInfo postInfo);

    /**
     * 用户回复帖子
     */
    @Insert("insert into ex_post(pl_parent_id,reply_user_id,reply_user_account,user_id,post_content,sp_url,tp_url,post_replylimit,post_time,post_ip,post_lan_ip," +
            "post_location,post_city,post_country,post_regionName,post_lat,post_lon)values(#{pl_parent_id},#{reply_user_id},#{reply_user_account},#{user_id},#{post_content}," +
            "#{sp_url},#{tp_url},#{post_replylimit},Now(),#{post_ip},#{post_lan_ip},#{post_location},#{post_city},#{post_country}," +
            "#{post_regionName},#{post_lat},#{post_lon})")
    public Integer addCommentPost(PostInfo postInfo);

    /**
     * 加载帖子 测试功能
     */
    @Select("SELECT \n" +
            "ex_user.user_id,ex_user.username,ex_user.user_nickname,ex_user.user_headimg,ex_user.user_bgimg,ex_user.user_isAlive,ex_user.user_lock,\n" +
            "ex_user.user_isBlock,ex_user.user_fo,ex_user.user_fans,ex_user.user_info,ex_user.auth_id,ex_post.*\n" +
            "FROM ex_user,ex_post\n" +
            "WHERE\n" +
            "ex_user.user_id = ex_post.user_id\n" +
            "ORDER BY post_time DESC;")
    public List<PostInfo> getPostData();

    /**
     * 加载推荐 贴文
     * 使用分页功能
     */
    @Select("SELECT\n" +
            "ex_user.user_id,ex_user.username,ex_user.user_nickname,ex_user.user_headimg,ex_user.user_bgimg,ex_user.user_isAlive,ex_user.user_lock,\n" +
            "ex_user.user_isBlock,ex_user.user_fo,ex_user.user_fans,ex_user.user_info,ex_user.user_regtime,ex_user.auth_id,ex_post.*,DATE_FORMAT(ex_post.post_time,'%Y年%m月%d日') as post_day,\n" +
            "DATE_FORMAT(ex_post.post_time,'%Y年%m月%d日 %H:%i:%s') as post_daytime\n" +
            "FROM ex_user,ex_post\n" +
            "WHERE\n" +
            "ex_user.user_id = ex_post.user_id\n" +
            "AND\n" +
            "ex_post.post_delete = 0\n" +
            "AND\n" +
            "ex_post.reply_user_id = 0\n" +
            "ORDER BY post_time DESC LIMIT #{startIndex},#{pageSize};")
    public List<PostInfo> getForYouPostDataByMapper(Pager pager);

    /**
     * 加载推荐 贴文
     * 计算分页数据总数
     */
    @Select("SELECT\n" +
            "count(*)\n" +
            "FROM ex_user,ex_post\n" +
            "WHERE\n" +
            "ex_user.user_id = ex_post.user_id\n" +
            "AND\n" +
            "ex_post.reply_user_id = 0\n" +
            "AND\n" +
            "ex_post.post_delete = 0")
    public Integer getForYouPostDataTotalCounts(Pager pager);

    /**
     * 加载指定用户贴文
     * 使用分页功能
     */
    @Select("SELECT\n" +
            "ex_user.user_id,ex_user.username,ex_user.user_nickname,ex_user.user_headimg,ex_user.user_bgimg,ex_user.user_isAlive,ex_user.user_lock,\n" +
            "ex_user.user_isBlock,ex_user.user_fo,ex_user.user_fans,ex_user.user_info,ex_user.user_regtime,ex_user.auth_id,ex_post.*,DATE_FORMAT(ex_post.post_time,'%Y年%m月%d日') as post_day,\n" +
            "DATE_FORMAT(ex_post.post_time,'%Y年%m月%d日 %H:%i:%s') as post_daytime\n" +
            "FROM ex_user,ex_post\n" +
            "WHERE\n" +
            "ex_user.user_id = ex_post.user_id\n" +
            "AND\n" +
            "ex_user.user_id = #{user_id} \n" +
            "AND\n" +
            "ex_post.post_delete = 0\n" +
            "ORDER BY post_time DESC LIMIT #{startIndex},#{pageSize};")
    public List<PostInfo> getUserPostDataByMapperAndUserId(Pager pager);

    /**
     * 加载指定用户贴文
     * 计算分页数据总数
     */
    @Select("SELECT\n" +
            "count(*)\n" +
            "FROM ex_user,ex_post\n" +
            "WHERE\n" +
            "ex_user.user_id = ex_post.user_id\n" +
            "AND\n" +
            "ex_post.post_delete = 0\n" +
            "AND\n" +
            "ex_user.user_id = #{user_id} ;")
    public Integer getUserPostDataTotalCounts(Pager pager);

    /**
     * 查看帖子
     *
     * @param 帖子id
     */
    @Select("SELECT\n" +
            "ex_user.user_id,ex_user.username,ex_user.user_nickname,ex_user.user_headimg,ex_user.user_bgimg,ex_user.user_isAlive,ex_user.user_lock,\n" +
            "ex_user.user_isBlock,ex_user.user_fo,ex_user.user_fans,ex_user.user_info,ex_user.auth_id,ex_post.*\n" +
            "FROM ex_user,ex_post\n" +
            "WHERE\n" +
            "ex_user.user_id = ex_post.user_id\n" +
            "AND\n" +
            "ex_post.post_delete = 0\n" +
            "AND\n" +
            "ex_post.post_id = #{post_id}")
    public PostInfo getPostInfoByPostId(PostInfo postInfo);

    /**
     * 查询当前登录用户对所浏览器帖子id是否进行了转发和喜欢操作，如果有数据则查询出来到前端进行
     * 样式切换和功能切换
     */
    @Select("SELECT * FROM ex_post_zf_xh_rel\n" +
            "where opr_user_id=#{opr_user_id} and post_id=#{post_id};")
    public PostInfo getPostRelationShipByPostIdAndCurrentUserId(PostInfo postInfo);

    /**
     * 点赞操作
     * 先查询user_id和post_id在关系表中是否存在
     * 存在则修改喜欢为1
     * 不存在则添加该条关系数据并设置喜欢为1
     */

    /**
     * 第一步判断是否存在关系数据
     */
    @Select("select count(*) from ex_post_zf_xh_rel\n" +
            "where opr_user_id=#{opr_user_id} and post_id=#{post_id};")
    public Integer isExistedPostRelationZFANDXH(PostInfo postInfo);

    /**
     * 添加喜欢关系数据
     */
    @Insert("insert into ex_post_zf_xh_rel(post_id,opr_user_id,xh_selected)values(#{post_id},#{opr_user_id},1)")
    public Integer addPostRelationXH(PostInfo postInfo);

    /**
     * 修改关系数据中的喜欢字段
     */
    @Update("update ex_post_zf_xh_rel set xh_selected=1 where opr_user_id=#{opr_user_id} and post_id=#{post_id}")
    public Integer updatePostRelationXH(PostInfo postInfo);


    /**
     * 取出关系数据中转发字段
     */
    @Select("select zf_selected from ex_post_zf_xh_rel where opr_user_id=#{opr_user_id} and post_id=#{post_id}")
    public Integer getPostRelationZF(PostInfo postInfo);

    /**
     * 如果转发操作为1，则把点赞设置为0
     */
    @Update("update ex_post_zf_xh_rel set xh_selected=0 where opr_user_id=#{opr_user_id} and post_id=#{post_id}")
    public Integer updatePostRelationCancelXH(PostInfo postInfo);

    /**
     * 删除关系数据
     */
    @Delete("delete from ex_post_zf_xh_rel where opr_user_id=#{opr_user_id} and post_id=#{post_id}")
    public Integer delPostRelation(PostInfo postInfo);


    /**
     * 帖子访问量
     * 每访问一次+1
     */
    @Update("update ex_post set fw_counts=fw_counts+1 where post_id=#{post_id}")
    public Integer addFWCounts(PostInfo postInfo);


    /**
     * 对帖子的转发数、评论数、点赞喜欢数量统计采用存储过程
     * 避免统计错误
     * 更新评论数量
     */
    @Update("UPDATE ex_post set pl_counts = \n" +
            "(SELECT t.counts FROM (SELECT count(*) as counts FROM ex_post where post_delete=0 and zf_parent_id=0 and pl_parent_id=#{post_id}) t) \n" +
            "where post_id=#{post_id}")
    public Integer updatePLCountsByPostId(PostInfo postInfo);

    /**
     * 更新转发数量
     */
    @Update("UPDATE ex_post set zf_counts = \n" +
            "(SELECT t.counts FROM (SELECT count(*) as counts FROM ex_post where post_delete=0 and pl_parent_id=0 and zf_parent_id=#{post_id}) t) \n" +
            "where post_id=#{post_id}")
    public Integer updateZFCountsByPostId(PostInfo postInfo);

    /**
     * 更新点赞喜欢数量
     */
    @Update("UPDATE ex_post set xh_counts = (SELECT count(*) FROM ex_post_zf_xh_rel where xh_selected=1 and post_id=#{post_id}) where post_id =#{post_id}")
    public Integer updateXHCountsByPostId(PostInfo postInfo);

    /**
     * 快转
     * 先查询user_id和post_id在关系表中是否存在
     * 存在则修改转发为1
     * 不存在则添加该条关系数据并设置转发为1
     * 帖子转发量+1
     */

    /**
     * 添加转发关系数据
     */
    @Insert("insert into ex_post_zf_xh_rel(post_id,opr_user_id,zf_selected)values(#{post_id},#{opr_user_id},1)")
    public Integer addPostRelationZF(PostInfo postInfo);

    /**
     * 修改关系数据中的转发字段
     */
    @Update("update ex_post_zf_xh_rel set zf_selected=1 where opr_user_id=#{opr_user_id} and post_id=#{post_id}")
    public Integer updatePostRelationZF(PostInfo postInfo);

    /**
     * 转发对应帖子转发量+1
     */
    @Update("update ex_post set zf_counts=zf_counts+1 where post_id=#{post_id}")
    public Integer updatePostAddZFCount(PostInfo postInfo);


    /**
     * 快转
     * 先查询该条关系的喜欢是否为1，为1则修改转发为0
     * 喜欢为0，则删除该条数据
     * 转发量-1
     */

    /**
     * 取出关系数据中喜欢点赞字段
     */
    @Select("select xh_selected from ex_post_zf_xh_rel where opr_user_id=#{opr_user_id} and post_id=#{post_id}")
    public Integer getPostRelationXH(PostInfo postInfo);

    /**
     * 如果点赞操作为1，则把转发设置为0
     */
    @Update("update ex_post_zf_xh_rel set zf_selected=0 where opr_user_id=#{opr_user_id} and post_id=#{post_id}")
    public Integer updatePostRelationCancelZF(PostInfo postInfo);

    /**
     * 删除关系数据
     * 通过zf_parent_id和user_id,以及zf_quick字段，查询post_id
     * 然后删除
     */
    @Delete("DELETE FROM ex_post WHERE post_id = (\n" +
            "select id from (SELECT post_id as id from ex_post WHERE zf_quick=1 AND zf_parent_id=#{zf_parent_id} and user_id=#{user_id}) t\n" +
            ")")
    public Integer delQuickQuotePost(PostInfo postInfo);

    /**
     * 取消快转对应帖子-1
     */
    @Update("update ex_post set zf_counts=zf_counts-1 where post_id=#{post_id}")
    public Integer updatePostDecreaseZFCount(PostInfo postInfo);

    /**
     * 评论量
     * 每回复一次+1
     */
    @Update("update ex_post set pl_counts=pl_counts+1 where post_id=#{post_id}")
    public Integer addCommentCounts(PostInfo postInfo);

    /**
     * 通过pl_parant_id查询评论列表，根据时间排序
     * 分页查询功能
     */
    @Select("SELECT\n" +
            "ex_user.user_id,ex_user.username,ex_user.user_nickname,ex_user.user_headimg,ex_user.user_bgimg,ex_user.user_isAlive,ex_user.user_lock,\n" +
            "ex_user.user_isBlock,ex_user.user_fo,ex_user.user_fans,ex_user.user_info,ex_user.auth_id,ex_post.*,DATE_FORMAT(ex_post.post_time,'%Y年%m月%d日') as post_day,\n" +
            "DATE_FORMAT(ex_post.post_time,'%Y年%m月%d日 %H:%i:%s') as post_daytime\n" +
            "FROM ex_user,ex_post \n" +
            "WHERE\n" +
            "ex_user.user_id = ex_post.user_id\n" +
            "AND\n" +
            "post_delete = 0\n" +
            "AND\n" +
            "pl_parent_id=#{pl_parent_id}\n" +
            "ORDER BY post_time DESC\n" +
            "LIMIT #{startIndex},#{pageSize};")
    public List<PostInfo> getCommentDataListPager(Pager pager);

    /**
     * 通过pl_parant_id查询回复数量
     */
    @Select("SELECT\n" +
            "count(*)\n" +
            "FROM ex_user,ex_post\n" +
            "WHERE\n" +
            "ex_user.user_id = ex_post.user_id\n" +
            "AND\n" +
            "post_delete = 0\n" +
            "AND\n" +
            "pl_parent_id=#{pl_parent_id};")
    public Integer getCommentDataListTotalCounts(Pager pager);

    /*************************************************************
     * 根据用户id
     * 获取回复和转发分页数据
     */
    @Select("SELECT\n" +
            "ex_user.user_id,ex_user.username,ex_user.user_nickname,ex_user.user_headimg,ex_user.user_bgimg,ex_user.user_isAlive,ex_user.user_lock,\n" +
            "ex_user.user_isBlock,ex_user.user_fo,ex_user.user_fans,ex_user.user_info,ex_user.auth_id,ex_post.*,DATE_FORMAT(ex_post.post_time,'%Y年%m月%d日') as post_day,\n" +
            "DATE_FORMAT(ex_post.post_time,'%Y年%m月%d日 %H:%i:%s') as post_daytime\n" +
            "FROM ex_user,ex_post \n" +
            "WHERE\n" +
            "ex_user.user_id = ex_post.user_id\n" +
            "AND\n" +
            "ex_post.post_delete=0\n" +
            "AND\n" +
            "(ex_post.pl_parent_id>0 or ex_post.zf_parent_id>0)\n" +
            "AND\n" +
            "ex_user.user_id= #{user_id} \n" +
            "ORDER BY post_time DESC LIMIT #{startIndex},#{pageSize};")
    public List<PostInfo> getReplyPostDataMapperByUserId(Pager pager);

    /**
     * 根据用户id
     * 获取回复和转发分页数据总数
     */
    @Select("SELECT count(*) FROM ex_user,ex_post\n" +
            "WHERE\n" +
            "ex_user.user_id = ex_post.user_id\n" +
            "AND\n" +
            "ex_post.post_delete=0\n" +
            "AND\n" +
            "(ex_post.pl_parent_id>0 or ex_post.zf_parent_id>0)\n" +
            "AND\n" +
            "ex_user.user_id= #{user_id} ")
    public Integer getReplyPostDataMapperTotalCounts(Pager pager);

    /*************************************************************
     * 获取用户喜欢的分页数据
     */
    @Select("SELECT\n" +
            "ex_user.user_id,ex_user.username,ex_user.user_nickname,ex_user.user_headimg,ex_user.user_bgimg,ex_user.user_isAlive,ex_user.user_lock,\n" +
            "ex_user.user_isBlock,ex_user.user_fo,ex_user.user_fans,ex_user.user_info,ex_user.auth_id,ex_post.*,DATE_FORMAT(ex_post.post_time,'%Y年%m月%d日') as post_day,\n" +
            "DATE_FORMAT(ex_post.post_time,'%Y年%m月%d日 %H:%i:%s') as post_daytime\n" +
            "FROM ex_user,ex_post \n" +
            "WHERE\n" +
            "ex_user.user_id = ex_post.user_id\n" +
            "AND\n" +
            "ex_post.post_delete=0\n" +
            "AND\n" +
            "ex_post.post_id in (SELECT post_id FROM ex_post_zf_xh_rel where opr_user_id=#{user_id})\n" +
            "ORDER BY post_time DESC LIMIT #{startIndex},#{pageSize};")
    public List<PostInfo> getLikePostDataMapperByUserId(Pager pager);

    /**
     * 获取用户喜欢的分页数据总数
     */
    @Select("SELECT count(*) FROM ex_user,ex_post\n" +
            "WHERE\n" +
            "ex_user.user_id = ex_post.user_id\n" +
            "AND\n" +
            "ex_post.post_delete=0\n" +
            "AND\n" +
            "ex_post.post_id in (SELECT post_id FROM ex_post_zf_xh_rel where opr_user_id=#{user_id});")
    public Integer getLikePostDataMapperTotalCounts(Pager pager);

    /*************************************************************
     * 获取用户多媒体数据分页
     * 包括图片和视频
     */
    @Select("SELECT\n" +
            "ex_user.user_id,ex_user.username,ex_user.user_nickname,ex_user.user_headimg,ex_user.user_bgimg,ex_user.user_isAlive,ex_user.user_lock,\n" +
            "ex_user.user_isBlock,ex_user.user_fo,ex_user.user_fans,ex_user.user_info,ex_user.auth_id,ex_post.*,DATE_FORMAT(ex_post.post_time,'%Y年%m月%d日') as post_day,\n" +
            "DATE_FORMAT(ex_post.post_time,'%Y年%m月%d日 %H:%i:%s') as post_daytime\n" +
            "FROM ex_user,ex_post \n" +
            "WHERE\n" +
            "ex_user.user_id = ex_post.user_id\n" +
            "AND\n" +
            "ex_post.post_delete=0\n" +
            "AND\n" +
            "((ex_post.tp_url <> '' AND ex_post.tp_url is NOT NULL) OR (ex_post.sp_url <> '' AND ex_post.sp_url is NOT NULL))\n" +
            "AND\n" +
            "ex_post.user_id = #{user_id}\n" +
            "ORDER BY post_time DESC LIMIT #{startIndex},#{pageSize};")
    public List<PostInfo> getMediaPostDataMapperByUserId(Pager pager);

    /**
     * 获取用户多媒体数据分页总数
     * 包括图片和视频
     */
    @Select("SELECT count(*) FROM ex_user,ex_post\n" +
            "WHERE\n" +
            "ex_user.user_id = ex_post.user_id\n" +
            "AND\n" +
            "ex_post.post_delete=0\n" +
            "AND\n" +
            "((ex_post.tp_url <> '' AND ex_post.tp_url is NOT NULL) OR (ex_post.sp_url <> '' AND ex_post.sp_url is NOT NULL))\n" +
            "AND\n" +
            "ex_post.user_id = #{user_id};")
    public Integer getMediaPostDataMapperTotalCounts(Pager pager);


    /*******************************************************
     *  查询带搜索条件的分页数据
     */
    @Select("SELECT\n" +
            "ex_user.user_id,ex_user.username,ex_user.user_nickname,ex_user.user_headimg,ex_user.user_bgimg,ex_user.user_isAlive,ex_user.user_lock,\n" +
            "ex_user.user_isBlock,ex_user.user_fo,ex_user.user_fans,ex_user.user_info,ex_user.auth_id,ex_post.*,DATE_FORMAT(ex_post.post_time,'%Y年%m月%d日') as post_day,\n" +
            "DATE_FORMAT(ex_post.post_time,'%Y年%m月%d日 %H:%i:%s') as post_daytime\n" +
            "FROM ex_user,ex_post \n" +
            "WHERE\n" +
            "ex_user.user_id = ex_post.user_id\n" +
            "AND\n" +
            "ex_post.post_delete=0\n" +
            "AND\n" +
            "ex_post.post_content like concat('%',#{query},'%')\n" +
            "ORDER BY post_time DESC LIMIT 0,10;")
    public List<PostInfo> getPostSearchMapperByQuery(Pager pager);


    /**
     * 查询带搜索条件的分页数据总条数
     */
    @Select("SELECT\n" +
            "count(*)\n" +
            "FROM ex_user,ex_post\n" +
            "WHERE\n" +
            "ex_user.user_id = ex_post.user_id\n" +
            "AND\n" +
            "ex_post.post_delete=0\n" +
            "AND\n" +
            "ex_post.post_content like concat('%',#{query},'%')")
    public Integer getPostSearchMapperCountByQuery(Pager pager);

    /**
     * 获取当前用户关注的人的帖子列表
     * 1.先获取当前用户关注的人列表，并转换字符串
     * 2.用条件语句 in 来查询
     */
    @Select("SELECT\n" +
            "ex_user.user_id,ex_user.username,ex_user.user_nickname,ex_user.user_headimg,ex_user.user_bgimg,ex_user.user_isAlive,ex_user.user_lock,\n" +
            "ex_user.user_isBlock,ex_user.user_fo,ex_user.user_fans,ex_user.user_info,ex_user.auth_id,ex_user.user_regtime,ex_post.*\n" +
            "FROM ex_user,ex_post\n" +
            "WHERE\n" +
            "ex_user.user_id = ex_post.user_id\n" +
            "AND\n" +
            "ex_post.post_delete=0\n" +
            "AND\n" +
            "ex_user.user_id in (${ids}) \n" +
            "ORDER BY post_time DESC LIMIT #{startIndex},#{pageSize};")
    public List<PostInfo> getPostDataMapperByCurrentUserFoUserIds(Pager pager);

    /**
     * 获取当前用户关注的人的帖子总数量
     * 用于计算分页
     */
    @Select("SELECT\n" +
            "count(*)\n" +
            "FROM ex_user,ex_post\n" +
            "WHERE\n" +
            "ex_user.user_id = ex_post.user_id\n" +
            "AND\n" +
            "ex_post.post_delete=0\n" +
            "AND\n" +
            "ex_user.user_id in (${ids}) ;")
    public Integer getPostDataCountByCurrentUserFoUserIds(Pager pager);


    /**
     * 删除自己的帖子
     * 这里是假删除，将帖子的字段post_delete设置为1
     */
    @Update("update ex_post set post_delete=1 where user_id=#{user_id} and post_id=#{post_id}")
    public Integer delPostByUserId(PostInfo postInfo);
}
